﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;

using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {

        public static UfVO getUf(string siglaUf, string uf)
        {
            UfVO ufVO = null;
            List<UfVO> listUfVOs = getUfs(siglaUf, uf);
            if (listUfVOs != null && listUfVOs.Count > 0)
            {
                ufVO = listUfVOs[0];
            }
            return ufVO;
        }

        public static List<UfVO> getUfs()
        {
            return getUfs(null,null);
        }

        public static List<UfVO> getUfs(string siglaUf, string uf)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT Sigla, Estado, Cadastro, CadastradoPor, Edicao, EditadoPor ");
            query.AppendLine("FROM uf ");
            if (siglaUf != null)
            {
                query.AppendLine(where + "Sigla=?Sigla ");
                where = "AND ";
            }
            if (uf != null)
            {
                query.AppendLine(where + "Estado LIKE ?Estado ");
                where = "AND ";
            }

            query.AppendLine("ORDER BY Estado ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (siglaUf != null) cmd.Parameters.AddWithValue("?Sigla", siglaUf);
            if (uf != null) cmd.Parameters.AddWithValue("?Estado", "%" + uf + "%");

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<UfVO> listUf = new List<UfVO>();

                while (reader.Read())
                {
                    UfVO ufVO = UfVO.CreateUfVO();
                    ufVO.siglaUf = reader.GetString(reader.GetOrdinal("Sigla"));
                    ufVO.uf = reader.GetString(reader.GetOrdinal("Estado"));
                    ufVO.cadastro = reader.GetDateTime(reader.GetOrdinal("Cadastro"));
                    ufVO.cadastradoPor = reader.GetString(reader.GetOrdinal("CadastradoPor"));
                    ufVO.edicao = reader.GetDateTime(reader.GetOrdinal("Edicao"));
                    ufVO.editadoPor = reader.GetString(reader.GetOrdinal("EditadoPor"));
                    listUf.Add(ufVO);
                }
                reader.Close();
                return listUf;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setUf(UfVO ufVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE uf ");
            query.AppendLine("SET Estado=?Estado, Cadastro=?Cadastro, CadastradoPor=?CadastradoPor, Edicao=?Edicao, EditadoPor=?EditadoPor ");
            query.AppendLine("WHERE Sigla=?Sigla ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Sigla", ufVO.siglaUf);
            cmd.Parameters.AddWithValue("?Estado", ufVO.uf);
            cmd.Parameters.AddWithValue("?Cadastro", ufVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", ufVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", ufVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", ufVO.editadoPor);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static string newUf(UfVO ufVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO uf ");
            query.AppendLine("  ( Sigla, Estado, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
            query.AppendLine("VALUES (?Sigla, ?Estado, ?Cadastro, ?CadastradoPor, ?Edicao, ?EditadoPor ) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Sigla", ufVO.siglaUf);
            cmd.Parameters.AddWithValue("?Estado", ufVO.uf);
            cmd.Parameters.AddWithValue("?Cadastro", ufVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", ufVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", ufVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", ufVO.editadoPor);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                return ufVO.siglaUf;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delUf(UfVO ufVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM uf ");
            query.AppendLine("WHERE Sigla=?Sigla ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Sigla", ufVO.siglaUf);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
    }
}
